<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_DICTIONARY_ITEM AS
(SELECT T.*,
row_number() over(order by T_DICTIONARY_TYPE.ORDER_NUM, T_DICTIONARY_TYPE.code, T_DICTIONARY.ORDER_NUM, T_DICTIONARY.code, t.ORDER_NUM, t.CODE) rn,
T_DICTIONARY_TYPE.ID AS typeId,
T_DICTIONARY_TYPE.code AS typeCode,
T_DICTIONARY_TYPE.name AS typeName,
t_DICTIONARY.code      AS dictionaryCode,
t_DICTIONARY.name      AS dictionaryName
FROM C1_DICTIONARY_ITEM T
LEFT JOIN C1_DICTIONARY t_DICTIONARY
ON (Sysdate BETWEEN t_DICTIONARY.begin_date AND t_DICTIONARY.end_date
AND t_DICTIONARY.id = t.dictionary_id)
LEFT JOIN C1_DICTIONARY_TYPE T_DICTIONARY_TYPE
ON (Sysdate BETWEEN T_DICTIONARY_TYPE.begin_date AND T_DICTIONARY_TYPE.end_date
AND T_DICTIONARY_TYPE.ID = t_DICTIONARY.type_id)
WHERE NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW())
<#if StringUtils.isNotBlank(typeCode)>
and (instr(','||T_DICTIONARY_TYPE.code||',', ','||:typeCode||',') > 0 or instr(','||T_DICTIONARY_TYPE.name||',', ','||:typeCode||',') > 0)
</#if>
<#if StringUtils.isNotBlank(dictionaryCode)>
and (instr(','||T_DICTIONARY.code||',', ','||:dictionaryCode||',') > 0 or instr(','||T_DICTIONARY.name||',', ','||:dictionaryCode||',') > 0)
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(','||T.code||',', ','||:code||',') > 0 or instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.*
FROM w_DICTIONARY_ITEM T
WHERE t.rn BETWEEN :begin_row_num AND :end_row_num